package com.panasonic.cn.cmsc;

import java.io.*;
import java.lang.*;
import java.util.*;
import java.math.*;
import java.sql.SQLException;
import com.fujitsu.fks.jaaf.core.*;

public class SQLLOGIN{
	private Hashtable paramsString     = new Hashtable();
	private Hashtable paramsBigDecimal = new Hashtable();
	private Hashtable paramsFree       = new Hashtable();
	private String lastSql_ = null;

	private String getString(String name){
		if(name==null) return "";
		String returnStr = (String)paramsString.get(name);
		if(returnStr==null) return "";
		return "'"+FrameworkCommon.stringReplaceSQuote(returnStr)+"'";
	}

	private String getBigDecimal(String name){
		if(name==null) return "";
		BigDecimal returnStr = (BigDecimal)paramsBigDecimal.get(name);
		if(returnStr==null) return "";
		return returnStr.toString();
	}

	private String getFree(String name){
		if(name==null) return "";
		String returnStr = (String)paramsFree.get(name);
		if(returnStr==null) return "";
		return returnStr;
	}

	public String getLastSql(){
		return lastSql_;
	}

	public SelectResultRset SELECT_LGN01(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT                                       ");
		sql.append("USER_ID,                                     ");
		sql.append("USER_NAME,                                   ");
		sql.append("PASSWORD,                                    ");
		sql.append("CTRL_DVSN_CD,                                ");
		sql.append("AUTH_LEVEL,                                  ");
		sql.append("BSNS_TYP,                                    ");
		sql.append("MAIL_ADDRESS,                                ");
		sql.append("MAIL_SIZE,                                   ");
		sql.append("L_DSP_NO,                                    ");
		sql.append("MENU_TYP,                 ");
		sql.append("USER_AB_ID,");
		sql.append("TO_CHAR(PSW_CHANGE_DTTM,'YYYYMMDD'),  ");
//add by lijun 20080114 start
		sql.append("PSW_TEST  ");
//add by lijun 20080114 end
		sql.append("FROM USR                                     ");
		sql.append("WHERE                                        ");
		sql.append("USER_ID "+((getString("USER_ID").equals(""))?" is null ":"=")+getString("USER_ID")+"                         ");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		SelectResultRset srr = db.executeQuery();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return srr;
	}

	public SelectResultRset SELECT_M0101(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT                                       ");
		sql.append("TRPT_NM_CN,                                  ");
		sql.append("TRPT_AB_CN,");
		sql.append("TRPT_CLSS_D_TYP                                    ");
		sql.append("FROM M01                                     ");
		sql.append("WHERE                                        ");
		sql.append("VDR_CD "+((getString("CTRL_DVSN_CD").equals(""))?" is null ":"=")+getString("CTRL_DVSN_CD")+"                     ");
		sql.append("AND                                          ");
		sql.append("APL_BDT <"+((getString("SYS_DATE").equals(""))?" is null ":"=")+getString("SYS_DATE")+"                       ");
		sql.append("AND                                          ");
		sql.append(""+getString("SYS_DATE")+" <= APL_EDT ");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		SelectResultRset srr = db.executeQuery();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return srr;
	}

	public int UPDATE_USR01(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("	UPDATE USR");
		sql.append("	SET ");
		sql.append("    PASSWORD "+((getString("PASSWORD").equals(""))?" is null ":"=")+getString("PASSWORD")+",");
		sql.append("    PSW_CHANGE_DTTM = TO_DATE("+getString("PSW_CHANGE_DTTM")+",'YYYYMMDDHH24MISS'),");
		sql.append("    UPD_DTTM = TO_DATE("+getString("UPD_DTTM")+",'YYYYMMDDHH24MISS'),");
		sql.append("    UPD_CHRG_CD "+((getString("UPD_CHRG_CD").equals(""))?" is null ":"=")+getString("UPD_CHRG_CD")+",        ");
		sql.append("    UPD_PGID "+((getString("UPD_PGID").equals(""))?" is null ":"=")+getString("UPD_PGID")+" ");
		sql.append("    WHERE USER_ID "+((getString("USER_ID").equals(""))?" is null ":"=")+getString("USER_ID")+"                       ");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		int updateCount = db.executeUpdate();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return updateCount;
	}


	public void setUSER_ID(String value){
		if(value==null) {
			paramsString.remove("USER_ID");
			return;
		}
		paramsString.put("USER_ID",value);
	}


	public void setCTRL_DVSN_CD(String value){
		if(value==null) {
			paramsString.remove("CTRL_DVSN_CD");
			return;
		}
		paramsString.put("CTRL_DVSN_CD",value);
	}


	public void setSYS_DATE(String value){
		if(value==null) {
			paramsString.remove("SYS_DATE");
			return;
		}
		paramsString.put("SYS_DATE",value);
	}


	public void setPASSWORD(String value){
		if(value==null) {
			paramsString.remove("PASSWORD");
			return;
		}
		paramsString.put("PASSWORD",value);
	}


	public void setPSW_CHANGE_DTTM(String value){
		if(value==null) {
			paramsString.remove("PSW_CHANGE_DTTM");
			return;
		}
		paramsString.put("PSW_CHANGE_DTTM",value);
	}


	public void setUPD_DTTM(String value){
		if(value==null) {
			paramsString.remove("UPD_DTTM");
			return;
		}
		paramsString.put("UPD_DTTM",value);
	}


	public void setUPD_CHRG_CD(String value){
		if(value==null) {
			paramsString.remove("UPD_CHRG_CD");
			return;
		}
		paramsString.put("UPD_CHRG_CD",value);
	}


	public void setUPD_PGID(String value){
		if(value==null) {
			paramsString.remove("UPD_PGID");
			return;
		}
		paramsString.put("UPD_PGID",value);
	}
//add by lijun 20080114 start 	
	public int UPDATE_PSW_TEST(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
		sql.append("usr ");
		sql.append("set ");
		sql.append("psw_test ");
		sql.append("= ");
		sql.append("usr.psw_test ");
		sql.append("+ ");
		sql.append("1 ");
		sql.append("    WHERE USER_ID "+((getString("USER_ID").equals(""))?" is null ":"=")+getString("USER_ID")+"                       ");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		int updateCount = db.executeUpdate();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return updateCount;
	}
	
	public SelectResultRset PSW_TEST(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT                                       ");
		sql.append("usr.psw_test                                  ");
		sql.append("FROM usr                                     ");
		sql.append("    WHERE USER_ID "+((getString("USER_ID").equals(""))?" is null ":"=")+getString("USER_ID")+"                       ");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		SelectResultRset srr = db.executeQuery();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return srr;
	}
	public int RELEASE_PSW_TEST(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
		sql.append("usr ");
		sql.append("set ");
		sql.append("psw_test ");
		sql.append("= ");
		sql.append("0 ");
		sql.append("    WHERE USER_ID "+((getString("USER_ID").equals(""))?" is null ":"=")+getString("USER_ID")+"                       ");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		int updateCount = db.executeUpdate();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return updateCount;
	}
//	add by lijun 20080114 end
	
	public int INSERT_PWD_LOG(FrameworkDbCommon db) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("	INSERT INTO PWD_LOG(");
		sql.append("	USER_ID,");
		sql.append("	MIS_PWD,");
		sql.append("	PSW_CHANGE_DTTM");
		sql.append("	)");
		sql.append("	values (");
		sql.append("	"+getString("USER_ID")+",");
		sql.append("	"+getString("MIS_PWD")+",");
		sql.append("	TO_DATE("+getString("PSW_CHANGE_DTTM")+",'YYYYMMDD HH24:MI:SS')");
		sql.append("	)");
		db.setSql(sql.toString());
		lastSql_ =sql.toString();
		int updateCount = db.executeUpdate();
		paramsString     = new Hashtable();
		paramsBigDecimal = new Hashtable();
		paramsFree       = new Hashtable();

		return updateCount;
	}
	
	public void setMIS_PWD(String value){
		if(value==null) {
			paramsString.remove("MIS_PWD");
			return;
		}
		paramsString.put("MIS_PWD",value);
	}
}
